import akshare as ak
import time
from sqlalchemy import create_engine
from sqlalchemy import text

start_date="20230101"
today = time.strftime("%Y-%m-%d", time.localtime(time.time()))
print(today)

def create_mysql_engine():
    """
    创建数据库引擎对象
    :return: 新创建的数据库引擎对象
    """
    # 引擎参数信息
    host = 'localhost'
    user = 'root'
    passwd = 'XXyy%402023'
    port = '3306'
    db = 'shares'
 
    # 创建连接数据库stock的引擎对象
    db_engine = create_engine(
        'mysql+pymysql://{0}:{1}@{2}:{3}/{4}?charset=utf8'.format(user, passwd, host, port, db))
 
    # 返回引擎对象
    return db_engine

def update():
    # 首先加载所有的股票代码，大概7000支
    df = ak.stock_zh_a_spot_em()
    engine = create_mysql_engine()
    con = engine.connect()

    # 先按照名称和代码，排除不想要的
    updated = 0
    for index, row in df.iterrows():
        code = row['代码']
        name = row['名称']
        # 排除ST开头的和8开头的
        if int(code) >= 800000 or 'ST' in name:
            continue

        table_name = 'stock_%s' % (code)
        has_table = engine.dialect.has_table(con, table_name)
        if not has_table:
            con.execute(text('CREATE TABLE `%s` (`日期` text,`开盘` double DEFAULT NULL,`收盘` double DEFAULT NULL, \
            `最高` double DEFAULT NULL,`最低` double DEFAULT NULL,`成交量` bigint DEFAULT NULL,`成交额` double DEFAULT NULL, \
            `振幅` double DEFAULT NULL,`涨跌幅` double DEFAULT NULL,`涨跌额` double DEFAULT NULL,`换手率` double DEFAULT NULL)' % (table_name)))
            print('new table %s' % (table_name))
        
        try:
            rs = con.execute(text('select * from %s order by 日期 desc limit 1' % (table_name)))
        except:
            con.execute(text('drop table %s' % (table_name)))
            continue

        if rs.rowcount >= 1:
            db_row = rs.first()._mapping
            if (db_row['开盘'] == row['今开']) and (db_row['收盘'] == row['最新价']) and (db_row['成交量'] == row['成交量']) and (db_row['涨跌幅'] == row['涨跌幅']) and (db_row['换手率'] == row['换手率']):
                continue # stock_zh_a_spot_em获取回来的没有日期，只有根据最后一列的一些字段是否与线上的一样，判断是否已经获取过

        rs = con.execute(text('SELECT * FROM %s where 日期=\'%s\'' % (table_name, today)))
        if rs.rowcount <= 0:
            val = '\'%s\',\'%.2f\',\'%.2f\',\'%.2f\',\'%.2f\',\'%.2f\',\'%.2f\',\'%.2f\',\'%.2f\',\'%.2f\',\'%.2f\'' %  \
                    (today, row['今开'], row['最新价'], row['最高'], row['最低'], row['成交量'], row['成交额'], row['振幅'], row['涨跌幅'], row['涨跌额'], row['换手率'])

            try:
                con.execute(text('insert into %s VALUE(%s)' % (table_name, val)))
                con.commit()
            except:
                continue


        updated += 1
    
    print('updated %d stocks' % (updated))

if __name__=='__main__':
    update()

